package com.railway.dao.impl;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.railway.bean.Carriage;
import com.railway.bean.Pass;
import com.railway.bean.Seat;
import com.railway.bean.Station;
import com.railway.bean.Train;
import com.railway.dao.TrainDao;
import com.railway.util.DBUtil;

public class TrainDaoImpl implements TrainDao {

	JdbcTemplate jt = new JdbcTemplate(DBUtil.getDataSource());
	@Override
	public List<Train> selectTrainIdByStation(int station_id1, int station_id2,List<Date> date) {
		//通过查询途径表和途径时间表来查询列车
		DateFormat dft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql ="select t1.train_id from pass t1 join pass t2 on t1.train_id = t2.train_id \r\n" + 
				"				join passtime t3 ON t1.train_id = t3.train_id\r\n" + 
				"				where t1.station_id = ? AND t2.station_id = ? AND t1.No<t2.No \r\n" + 
				"				AND t1.pass_id = t3.pass_id\r\n" + 
				"				AND t3.pass_time between ? and ? \r\n" + 
				"				group by t1.train_id";
		return jt.query(sql,  new BeanPropertyRowMapper<Train>(Train.class),station_id1,station_id2,
				dft.format(date.get(0)),dft.format(date.get(1)));
	}
	
	@Override
	public List<Integer> selectStation(String keyword) {
		String sql = "select * from station where (station_name like ? or station_city like ?)";
		List<Station> li = jt.query(sql, new BeanPropertyRowMapper<Station>(Station.class),"%"+keyword+"%","%"+keyword+"%");
		List<Integer> li2 = new ArrayList();
		for (Station station : li) {
			li2.add(station.getStation_id());
		}
		return li2;
	}

	@Override
	public List<Train> selectTranById(int Train_id) {
		String sql ="select * from train where train_id = ?";
		return jt.query(sql, new BeanPropertyRowMapper<Train>(Train.class), Train_id);
	}

	@Override
	public List<Carriage> selectCarriage(int train_id) {
		String sql = "select * from carriage where train_id = ?";
		return jt.query(sql, new BeanPropertyRowMapper<Carriage>(Carriage.class), train_id);
	}

	@Override
	public List<Seat> selectSeat(int train_id, int camage_num) {
		String sql = "select * from seat where train_id = ? and camage_num = ?";
		return jt.query(sql, new BeanPropertyRowMapper<Seat>(Seat.class),train_id,camage_num);
	}

	@Override
	public List<Pass> selectPass(int train_id) {
		String sql = "select t1.train_id,t1.pass_id,t1.station_id,t1.`No`,t1.next_distance,t2.pass_time,t2.stading_time,t2.is_pass,t3.station_name " + 
				"from pass t1 join passtime t2 on  t1.train_id = t2.train_id  JOIN station t3 on t1.station_id = t3.station_id " + 
				" where t1.train_id = ? and t1.pass_id = t2.pass_id";
		return jt.query(sql, new BeanPropertyRowMapper<Pass>(Pass.class),train_id);
	}

	@Override
	public int selectTrainCount() {
		String sql = "select count(1) from train";
		return jt.queryForObject(sql, Integer.class);
	}

	@Override
	public int insertTrain(Train train) {
		String sql = "insert into train(train_id,train_name,train_type,starting_station_id,"
				+ "terminal_station_id,starting_time,terminal_time,train_size) "
				+ "values(?,?,?,?,?,?,?,?)";
		return jt.update(sql,train.getTrain_id(),train.getTrain_name(),train.getTrain_type(),train.getStarting_station_id(),
				train.getTerminal_station_id(),train.getStarting_time(),train.getTerminal_time(),train.getTrain_size());
	}

	@Override
	public int insertCarriage(Carriage carriage) {
		String sql = "insert into camage(train_id,camage_num,camage_type) values(?,?,?)";
		return jt.update(sql,carriage.getTrain_id(),carriage.getCarriage_num(),carriage.getCarriage_type());
	}

	@Override
	public int insertSeat(Seat seat) {
		String sql = "insert into seat(train_id,camage_num,seat_num,seat_localtion) values(?,?,?,?)";
		return jt.update(sql, seat.getCamage_num(),seat.getSeat_num(),
				seat.getSeat_location());
	}

	@Override
	public int insertPass(Pass pass) {
		String sql = "insert into pass(pass_id,train_id,station_id,No,pass_time) values(?,?,?,?,?) ";
		 
		return jt.update(sql,pass.getPass_id(),pass.getTrain_id(),pass.getStation_id(),pass.getNo(),pass.getPass_time());
	}

	@Override
	public String selectStationById(int station_id) {
		String sql ="select * from station where station_id = ?";
		System.out.println(station_id);
		List<Station> li = jt.query(sql, new BeanPropertyRowMapper<Station>(Station.class), station_id);
		if(li.size()>0) {
			return li.get(0).getStation_name();
		}
		return "";
	}

	@Override
	public int selectStation_id(String station) {
		String sql ="select * from station where station_name = ?";
		List<Station> li = jt.query(sql, new BeanPropertyRowMapper<Station>(Station.class), station);
		return li.size()>0 ? li.get(0).getStation_id():0;
	}

	@Override
	public int deleteTrain(int train_id) {
		String sql ="delete from train where train_id = ?";
		return jt.update(sql, train_id);
	}

	@Override
	public int deleteCarriage(int train_id) {
		String sql ="delete from camage where train_id = ?";
		return jt.update(sql, train_id);
	}

	@Override
	public int deletePass(int train_id) {
		String sql ="delete from pass where train_id = ?";
		return jt.update(sql, train_id);
	}

	@Override
	public int deleteSeat(int train_id) {
		String sql ="delete from seat where train_id = ?";
		return jt.update(sql, train_id);
	}

	@Override
	public List<Train> selectTrainByTrainName(String train_name) {
		String sql = "select * from train where train_name = ?";
		return jt.query(sql, new BeanPropertyRowMapper<Train>(Train.class),train_name);
	}

	@Override
	public int selectPass_id(int station_id, int train_id) {
		String sql = "select * from pass where train_id = ? and station_id = ?";
		List<Pass> li = jt.query(sql, new BeanPropertyRowMapper<Pass>(Pass.class),train_id,station_id);
		return li.get(0).getPass_id();
	}

	@Override
	public List<Train> selectTrainAll(int pageNum, int pageSize) {
		String sql = "select * from Train limit ?,?";
		List<Train> li = jt.query(sql, new BeanPropertyRowMapper<Train>(Train.class),(pageNum-1)*pageSize,pageSize);
		return li;
	}

	@Override
	public int insertPassTime(int train_id,String pass_time,int stading_time) {
		String sql = "inser into passtime(train_id,pass_time,stading_time)";
		return jt.update(sql,train_id,pass_time,stading_time);
	}

	@Override
	public int insertTrainTime(int train_id, String starting_time, String terminal_time) {
		String sql = "insert into train_time(train_id,starting_time,terminal_time) values(?,?,?)";
		if(!"".equals(starting_time)&&!"".equals(terminal_time)) {
			return jt.update(sql,train_id,starting_time,terminal_time);
		}else{
			return 0;
		}
	}
}
